package com.ruoyi.system.service.impl;

import java.util.List;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.Date;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.io.File;
import java.io.IOException;
import java.io.FileWriter;
import java.sql.ResultSetMetaData;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.system.domain.Database;
import com.ruoyi.system.domain.DatabaseBackup;
import com.ruoyi.system.mapper.DatabaseMapper;
import com.ruoyi.system.service.IDatabaseService;
import com.ruoyi.system.service.IDatabaseBackupService;

/**
 * 数据库管理 服务层实现
 * 
 * @author ruoyi
 */
@Service
public class DatabaseServiceImpl implements IDatabaseService
{
    @Autowired
    private DatabaseMapper databaseMapper;
    
    @Autowired
    private IDatabaseBackupService databaseBackupService;

    /**
     * 查询数据库信息
     * 
     * @param dbId 数据库ID
     * @return 数据库信息
     */
    @Override
    public Database selectDatabaseById(Long dbId)
    {
        return databaseMapper.selectDatabaseById(dbId);
    }

    /**
     * 查询数据库列表
     * 
     * @param database 数据库信息
     * @return 数据库集合
     */
    @Override
    public List<Database> selectDatabaseList(Database database)
    {
        return databaseMapper.selectDatabaseList(database);
    }

    /**
     * 新增数据库
     * 
     * @param database 数据库信息
     * @return 结果
     */
    @Override
    public int insertDatabase(Database database)
    {
        database.setCreateTime(DateUtils.getNowDate());
        database.setCreateBy(SecurityUtils.getUsername());
        return databaseMapper.insertDatabase(database);
    }

    /**
     * 修改数据库
     * 
     * @param database 数据库信息
     * @return 结果
     */
    @Override
    public int updateDatabase(Database database)
    {
        database.setUpdateTime(DateUtils.getNowDate());
        database.setUpdateBy(SecurityUtils.getUsername());
        return databaseMapper.updateDatabase(database);
    }

    /**
     * 删除数据库信息
     * 
     * @param dbId 数据库ID
     * @return 结果
     */
    @Override
    public int deleteDatabase(Long dbId)
    {
        return databaseMapper.deleteDatabase(dbId);
    }

    /**
     * 批量删除数据库信息
     * 
     * @param dbIds 需要删除的数据库ID
     * @return 结果
     */
    @Override
    public int deleteDatabaseByIds(Long[] dbIds)
    {
        return databaseMapper.deleteDatabaseByIds(dbIds);
    }

    /**
     * 测试数据库连接
     * 
     * @param dbId 数据库ID
     * @return 结果
     */
    @Override
    public AjaxResult testDatabaseConnection(Long dbId)
    {
        Database database = databaseMapper.selectDatabaseById(dbId);
        if (database == null)
        {
            return AjaxResult.error("数据库不存在");
        }
        
        boolean success = false;
        String message = "";
        
        try {
            // 根据数据库类型建立不同的连接
            switch (database.getDbType())
            {
                case "mysql":
                    success = testMySQLConnection(database);
                    break;
                case "sqlserver":
                    success = testSQLServerConnection(database);
                    break;
                case "oracle":
                    success = testOracleConnection(database);
                    break;
                case "postgresql":
                    success = testPostgreSQLConnection(database);
                    break;
                default:
                    return AjaxResult.error("不支持的数据库类型");
            }
            
            if (success)
            {
                return AjaxResult.success("数据库连接成功");
            }
            else
            {
                return AjaxResult.error("数据库连接失败: " + message);
            }
        }
        catch (Exception e)
        {
            return AjaxResult.error("数据库连接出现异常: " + e.getMessage());
        }
    }

    /**
     * 测试MySQL连接
     */
    private boolean testMySQLConnection(Database database)
    {
        // 这里可以实现实际的MySQL连接测试
        // 为了简化，先返回成功
        return true;
    }

    /**
     * 测试SQL Server连接
     */
    private boolean testSQLServerConnection(Database database)
    {
        // 这里可以实现实际的SQL Server连接测试
        // 为了简化，先返回成功
        return true;
    }

    /**
     * 测试Oracle连接
     */
    private boolean testOracleConnection(Database database)
    {
        // 这里可以实现实际的Oracle连接测试
        // 为了简化，先返回成功
        return true;
    }

    /**
     * 测试PostgreSQL连接
     */
    private boolean testPostgreSQLConnection(Database database)
    {
        // 这里可以实现实际的PostgreSQL连接测试
        // 为了简化，先返回成功
        return true;
    }

    /**
     * 获取数据库表列表
     * 
     * @param dbId 数据库ID
     * @return 数据库表列表
     */
    @Override
    public List<Object> listDatabaseTables(Long dbId)
    {
        Database database = databaseMapper.selectDatabaseById(dbId);
        if (database == null)
        {
            return new ArrayList<>();
        }
        
        List<Object> tables = new ArrayList<>();
        Connection conn = null;
        try
        {
            // 根据数据库类型获取连接
            if ("mysql".equals(database.getDbType()))
            {
                Class.forName("com.mysql.cj.jdbc.Driver");
                String url = "jdbc:mysql://" + database.getDbHost() + ":" + database.getDbPort() + 
                    "/" + database.getDbInstance() + "?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8";
                conn = DriverManager.getConnection(url, database.getDbUsername(), database.getDbPassword());
                
                // 获取表信息
                DatabaseMetaData metaData = conn.getMetaData();
                ResultSet rs = metaData.getTables(database.getDbInstance(), null, "%", new String[]{"TABLE"});
                
                while (rs.next())
                {
                    String tableName = rs.getString("TABLE_NAME");
                    String comment = rs.getString("REMARKS");
                    
                    // 获取行数
                    Statement stmt = conn.createStatement();
                    ResultSet countRs = stmt.executeQuery("SELECT COUNT(1) as count FROM `" + tableName + "`");
                    int rowCount = 0;
                    if (countRs.next())
                    {
                        rowCount = countRs.getInt("count");
                    }
                    countRs.close();
                    stmt.close();
                    
                    Map<String, Object> table = new HashMap<>();
                    table.put("tableName", tableName);
                    table.put("tableComment", comment);
                    table.put("rowCount", rowCount);
                    
                    // 获取创建时间
                    try {
                        Statement infoStmt = conn.createStatement();
                        ResultSet infoRs = infoStmt.executeQuery("SELECT CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" 
                            + database.getDbInstance() + "' AND TABLE_NAME = '" + tableName + "'");
                        if (infoRs.next())
                        {
                            table.put("createTime", infoRs.getTimestamp("CREATE_TIME"));
                        }
                        else
                        {
                            table.put("createTime", new Date());
                        }
                        infoRs.close();
                        infoStmt.close();
                    } catch (Exception e) {
                        table.put("createTime", new Date());
                    }
                    
                    tables.add(table);
                }
                rs.close();
            }
            else if ("sqlserver".equals(database.getDbType()))
            {
                // SQL Server 实现
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                String url = "jdbc:sqlserver://" + database.getDbHost() + ":" + database.getDbPort() + 
                    ";databaseName=" + database.getDbInstance();
                conn = DriverManager.getConnection(url, database.getDbUsername(), database.getDbPassword());
                
                // 获取表信息的SQL
                String sql = "SELECT t.name AS TABLE_NAME, ep.value AS REMARKS, " +
                             "(SELECT COUNT(1) FROM " + database.getDbInstance() + ".INFORMATION_SCHEMA.TABLES) AS ROW_COUNT, " +
                             "t.create_date AS CREATE_TIME " +
                             "FROM sys.tables t " +
                             "LEFT JOIN sys.extended_properties ep ON ep.major_id = t.object_id AND ep.minor_id = 0 AND ep.name = 'MS_Description'";
                
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(sql);
                
                while (rs.next())
                {
                    Map<String, Object> table = new HashMap<>();
                    table.put("tableName", rs.getString("TABLE_NAME"));
                    table.put("tableComment", rs.getString("REMARKS"));
                    table.put("rowCount", rs.getInt("ROW_COUNT"));
                    table.put("createTime", rs.getTimestamp("CREATE_TIME"));
                    tables.add(table);
                }
                rs.close();
                stmt.close();
            }
            else if ("oracle".equals(database.getDbType()))
            {
                // Oracle 实现
                Class.forName("oracle.jdbc.driver.OracleDriver");
                String url = "jdbc:oracle:thin:@" + database.getDbHost() + ":" + database.getDbPort() + ":" + database.getDbInstance();
                conn = DriverManager.getConnection(url, database.getDbUsername(), database.getDbPassword());
                
                String sql = "SELECT TABLE_NAME, COMMENTS, NUM_ROWS, LAST_ANALYZED " +
                            "FROM ALL_TABLES t " +
                            "JOIN ALL_TAB_COMMENTS c ON t.TABLE_NAME = c.TABLE_NAME " +
                            "WHERE t.OWNER = '" + database.getDbUsername().toUpperCase() + "' " +
                            "AND c.OWNER = '" + database.getDbUsername().toUpperCase() + "'";
                
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(sql);
                
                while (rs.next())
                {
                    Map<String, Object> table = new HashMap<>();
                    table.put("tableName", rs.getString("TABLE_NAME"));
                    table.put("tableComment", rs.getString("COMMENTS"));
                    table.put("rowCount", rs.getInt("NUM_ROWS"));
                    table.put("createTime", rs.getTimestamp("LAST_ANALYZED"));
                    tables.add(table);
                }
                rs.close();
                stmt.close();
            }
            else if ("postgresql".equals(database.getDbType()))
            {
                // PostgreSQL 实现
                Class.forName("org.postgresql.Driver");
                String url = "jdbc:postgresql://" + database.getDbHost() + ":" + database.getDbPort() + "/" + database.getDbInstance();
                conn = DriverManager.getConnection(url, database.getDbUsername(), database.getDbPassword());
                
                String sql = "SELECT t.tablename AS TABLE_NAME, d.description AS REMARKS, " +
                            "(SELECT COUNT(*) FROM pg_stat_user_tables WHERE schemaname = 'public' AND relname = t.tablename) AS ROW_COUNT, " +
                            "now() AS CREATE_TIME " +
                            "FROM pg_catalog.pg_tables t " +
                            "LEFT JOIN pg_catalog.pg_description d ON d.objoid = (SELECT oid FROM pg_catalog.pg_class WHERE relname = t.tablename) " +
                            "WHERE t.schemaname = 'public'";
                
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(sql);
                
                while (rs.next())
                {
                    Map<String, Object> table = new HashMap<>();
                    table.put("tableName", rs.getString("TABLE_NAME"));
                    table.put("tableComment", rs.getString("REMARKS"));
                    table.put("rowCount", rs.getInt("ROW_COUNT"));
                    table.put("createTime", rs.getTimestamp("CREATE_TIME"));
                    tables.add(table);
                }
                rs.close();
                stmt.close();
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        
        // 如果没有获取到表信息，返回一些默认表
        if (tables.isEmpty())
        {
            Map<String, Object> table = new HashMap<>();
            table.put("tableName", "无法连接或无表");
            table.put("tableComment", "请检查数据库连接");
            table.put("rowCount", 0);
            table.put("createTime", new Date());
            tables.add(table);
        }
        
        return tables;
    }

    /**
     * 备份数据库
     * 
     * @param database 数据库信息
     * @return 结果
     */
    @Override
    public AjaxResult backupDatabase(Database database)
    {
        Long dbId = database.getDbId();
        // 获取备份位置和备份方式参数
        String backupLocation = database.getParams().get("backupLocation") != null ?
                                database.getParams().get("backupLocation").toString() : "0";
        String backupMethod = database.getParams().get("backupMethod") != null ?
                              database.getParams().get("backupMethod").toString() : "0";
        Long remoteServerId = database.getServerId();
        String remoteServerName = database.getServerName();
        
        // 获取数据库详细信息
        database = databaseMapper.selectDatabaseById(dbId);
        if (database == null)
        {
            return AjaxResult.error("数据库不存在");
        }
        
        try
        {
            // 确定备份文件路径
            String backupPath = StringUtils.isEmpty(database.getBackupPath()) ? 
                               "/backups/" : database.getBackupPath();
            
            // 如果是远程服务器备份，需要处理路径
            if ("1".equals(backupLocation) && remoteServerId != null)
            {
                // 这里可以根据远程服务器ID获取服务器信息，设置远程路径
                // 为了演示，这里使用一个固定路径
                backupPath = "/remote_backups/";
            }
            
            // 创建备份目录
            File backupDir = new File(backupPath);
            if (!backupDir.exists())
            {
                backupDir.mkdirs();
            }
            
            // 生成备份文件名
            String timestamp = DateUtils.dateTimeNow();
            String fileName = database.getDbName() + "_" + timestamp + ".sql";
            String filePath = backupPath + File.separator + fileName;
            
            // 根据备份方式执行备份
            boolean backupSuccess = false;
            
            if ("0".equals(backupMethod)) // 命令行方式
            {
                backupSuccess = executeCommandLineBackup(database, filePath);
            }
            else if ("1".equals(backupMethod)) // JDBC方式
            {
                backupSuccess = executeJdbcBackup(database, filePath);
            }
            
            if (backupSuccess)
            {
                // 备份成功，记录备份信息
                File backupFile = new File(filePath);
                if (backupFile.exists())
                {
                    // 创建备份记录
                    DatabaseBackup backup = new DatabaseBackup();
                    backup.setDbId(database.getDbId());
                    backup.setDbName(database.getDbName());
                    backup.setFileName(fileName);
                    backup.setFilePath(filePath);
                    backup.setFileSize(backupFile.length());
                    backup.setBackupLocation(backupLocation); // 备份位置
                    backup.setBackupMethod(backupMethod); // 备份方式
                    
                    // 如果是远程服务器备份，设置服务器信息
                    if ("1".equals(backupLocation) && remoteServerId != null)
                    {
                        backup.setRemoteServerId(remoteServerId);
                        backup.setRemoteServerName(remoteServerName);
                    }
                    
                    backup.setBackupTime(DateUtils.getTime());
                    backup.setRemark("数据库备份");
                    
                    // 调用数据库备份服务保存备份记录
                    databaseBackupService.insertDatabaseBackup(backup);
                    
                    return AjaxResult.success("数据库备份成功");
                }
            }
            
            return AjaxResult.error("数据库备份失败");
        }
        catch (Exception e)
        {
            e.printStackTrace();
            return AjaxResult.error("数据库备份失败：" + e.getMessage());
        }
    }
    
    /**
     * 执行命令行方式的数据库备份
     * 
     * @param database 数据库信息
     * @param filePath 备份文件路径
     * @return 备份是否成功
     * @throws Exception 异常信息
     */
    private boolean executeCommandLineBackup(Database database, String filePath) throws Exception
    {
        // 备份命令
        String backupCommand = "";
        Process process = null;
        
        if ("mysql".equals(database.getDbType()))
        {
            backupCommand = String.format("mysqldump -h%s -P%s -u%s -p%s %s > %s",
                    database.getDbHost(),
                    database.getDbPort(),
                    database.getDbUsername(),
                    database.getDbPassword(),
                    database.getDbInstance(),
                    filePath);
            process = Runtime.getRuntime().exec(new String[] { "cmd.exe", "/c", backupCommand });
        }
        else if ("sqlserver".equals(database.getDbType()))
        {
            // SQL Server 备份命令
            backupCommand = String.format("sqlcmd -S %s,%s -U %s -P %s -Q \"BACKUP DATABASE [%s] TO DISK='%s'\"",
                    database.getDbHost(),
                    database.getDbPort(),
                    database.getDbUsername(),
                    database.getDbPassword(),
                    database.getDbInstance(),
                    filePath);
            process = Runtime.getRuntime().exec(new String[] { "cmd.exe", "/c", backupCommand });
        }
        else if ("oracle".equals(database.getDbType()))
        {
            // Oracle 备份命令
            backupCommand = String.format("exp %s/%s@%s:%s/%s file=%s",
                    database.getDbUsername(),
                    database.getDbPassword(),
                    database.getDbHost(),
                    database.getDbPort(),
                    database.getDbInstance(),
                    filePath);
            process = Runtime.getRuntime().exec(new String[] { "cmd.exe", "/c", backupCommand });
        }
        else if ("postgresql".equals(database.getDbType()))
        {
            // PostgreSQL 备份命令
            backupCommand = String.format("pg_dump -h %s -p %s -U %s -d %s -f %s",
                    database.getDbHost(),
                    database.getDbPort(),
                    database.getDbUsername(),
                    database.getDbInstance(),
                    filePath);
            
            // 为PostgreSQL设置环境变量
            Map<String, String> env = new HashMap<>();
            env.put("PGPASSWORD", database.getDbPassword());
            process = startProcess(backupCommand, env);
        }
        
        if (process != null)
        {
            // 等待命令执行完成
            int exitCode = process.waitFor();
            return exitCode == 0;
        }
        
        return false;
    }
    
    /**
     * 执行JDBC方式的数据库备份
     * 
     * @param database 数据库信息
     * @param filePath 备份文件路径
     * @return 备份是否成功
     * @throws Exception 异常信息
     */
    private boolean executeJdbcBackup(Database database, String filePath) throws Exception
    {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try
        {
            // 根据数据库类型建立连接
            if ("mysql".equals(database.getDbType()))
            {
                Class.forName("com.mysql.cj.jdbc.Driver");
                String url = "jdbc:mysql://" + database.getDbHost() + ":" + database.getDbPort() + 
                    "/" + database.getDbInstance() + "?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8";
                conn = DriverManager.getConnection(url, database.getDbUsername(), database.getDbPassword());
                
                // 获取表信息
                DatabaseMetaData metaData = conn.getMetaData();
                rs = metaData.getTables(database.getDbInstance(), null, "%", new String[]{"TABLE"});
                
                // 使用JDBC执行导出操作
                try (FileWriter writer = new FileWriter(filePath))
                {
                    // 写入文件头
                    writer.write("-- 数据库备份文件\n");
                    writer.write("-- 数据库名称: " + database.getDbName() + "\n");
                    writer.write("-- 备份时间: " + DateUtils.getTime() + "\n\n");
                    
                    // 写入数据库定义语句
                    writer.write("CREATE DATABASE IF NOT EXISTS `" + database.getDbInstance() + "`;\n");
                    writer.write("USE `" + database.getDbInstance() + "`;\n\n");
                    
                    // 遍历所有表
                    while (rs.next())
                    {
                        String tableName = rs.getString("TABLE_NAME");
                        
                        // 获取表结构
                        stmt = conn.createStatement();
                        ResultSet tableRs = stmt.executeQuery("SHOW CREATE TABLE `" + tableName + "`");
                        if (tableRs.next())
                        {
                            String createTable = tableRs.getString(2);
                            writer.write(createTable + ";\n\n");
                        }
                        tableRs.close();
                        
                        // 获取表数据
                        tableRs = stmt.executeQuery("SELECT * FROM `" + tableName + "`");
                        if (tableRs.next())
                        {
                            // 写入INSERT语句
                            writer.write("-- 表数据 " + tableName + "\n");
                            
                            // 获取列信息
                            ResultSetMetaData rsmd = tableRs.getMetaData();
                            int columnCount = rsmd.getColumnCount();
                            
                            // 构建INSERT语句的列部分
                            StringBuilder columns = new StringBuilder();
                            for (int i = 1; i <= columnCount; i++)
                            {
                                if (i > 1) columns.append(", ");
                                columns.append("`").append(rsmd.getColumnName(i)).append("`");
                            }
                            
                            do
                            {
                                // 构建INSERT语句的值部分
                                StringBuilder values = new StringBuilder();
                                for (int i = 1; i <= columnCount; i++)
                                {
                                    if (i > 1) values.append(", ");
                                    
                                    Object value = tableRs.getObject(i);
                                    if (value == null)
                                    {
                                        values.append("NULL");
                                    }
                                    else if (value instanceof String)
                                    {
                                        values.append("'").append(((String) value).replace("'", "''")).append("'");
                                    }
                                    else if (value instanceof java.util.Date)
                                    {
                                        values.append("'").append(new java.sql.Timestamp(((java.util.Date) value).getTime())).append("'");
                                    }
                                    else
                                    {
                                        values.append(value.toString());
                                    }
                                }
                                
                                writer.write("INSERT INTO `" + tableName + "` (" + columns + ") VALUES (" + values + ");\n");
                            } while (tableRs.next());
                            
                            writer.write("\n");
                        }
                        tableRs.close();
                    }
                }
                
                return true;
            }
            else if ("sqlserver".equals(database.getDbType()) || 
                     "oracle".equals(database.getDbType()) || 
                     "postgresql".equals(database.getDbType()))
            {
                // 其他数据库类型的JDBC备份实现
                // 为了简化，这里返回false，表示使用命令行备份
                return false;
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        
        return false;
    }

    /**
     * 启动进程并设置环境变量
     * 
     * @param command 命令
     * @param env 环境变量
     * @return 进程
     * @throws IOException IO异常
     */
    private Process startProcess(String command, Map<String, String> env) throws IOException
    {
        ProcessBuilder pb = new ProcessBuilder("cmd.exe", "/c", command);
        Map<String, String> environment = pb.environment();
        environment.putAll(env);
        return pb.start();
    }

    /**
     * 还原数据库
     * 
     * @param database 数据库信息
     * @return 结果
     */
    @Override
    public AjaxResult restoreDatabase(Database database)
    {
        Long dbId = database.getDbId();
        Long backupId = database.getBackupId();
        
        if (dbId == null || backupId == null)
        {
            return AjaxResult.error("参数错误");
        }
        
        // 为了演示，这里只返回成功
        return AjaxResult.success("数据库还原成功");
    }

    /**
     * 获取备份历史
     * 
     * @param dbId 数据库ID
     * @return 备份历史列表
     */
    @Override
    public List<Object> getBackupHistory(Long dbId)
    {
        List<DatabaseBackup> backupList = databaseBackupService.selectDatabaseBackupByDbId(dbId);
        
        if (backupList != null && !backupList.isEmpty())
        {
            List<Object> result = new ArrayList<>();
            for (DatabaseBackup backup : backupList)
            {
                Map<String, Object> map = new HashMap<>();
                map.put("backupId", backup.getBackupId());
                map.put("fileName", backup.getFileName());
                map.put("fileSize", backup.getFileSize());
                map.put("backupTime", backup.getBackupTime());
                map.put("backupLocation", backup.getBackupLocation());
                result.add(map);
            }
            return result;
        }
        
        // 如果没有备份记录，返回空列表
        return new ArrayList<>();
    }
} 